package com.cy;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Date;

public class ExcleReadTest {

	String PATH = "F:\\work\\CODES\\easyexcel\\pio/";

	@Test
	public void testRead03() throws Exception {
		//获取文件流
		FileInputStream inputStream = new FileInputStream(PATH + "观众统计表03.xls");
		//1,创建工作簿 代表excle能操作的他都能操作
		Workbook workbook = new HSSFWorkbook(inputStream);
		//2,获取表
		Sheet sheetAt = workbook.getSheetAt(0);
		//3,获取第一行
		Row row = sheetAt.getRow(0);
		//4,获取第一列
		Cell cell = row.getCell(0);

		//5,取出(1,1)的值 字符串类型
		String stringCellValue = cell.getStringCellValue();
//		double numericCellValue = cell.getNumericCellValue();
		System.out.println(stringCellValue);
		//关闭流
		inputStream.close();
	}

	@Test
	public void testRead07() throws Exception {
		//获取文件流
		FileInputStream inputStream = new FileInputStream(PATH + "观众统计表07.xlsx");
		//1,创建工作簿 代表excle能操作的他都能操作
		Workbook workbook = new XSSFWorkbook(inputStream);
		//2,获取表
		Sheet sheetAt = workbook.getSheetAt(0);
		//3,获取第一行
		Row row = sheetAt.getRow(0);
		//4,获取第一列
		Cell cell = row.getCell(1);

		//5,取出(1,1)的值 字符串类型
		String stringCellValue = cell.getStringCellValue();
//		double numericCellValue = cell.getNumericCellValue();
		System.out.println(stringCellValue);
		//关闭流
		inputStream.close();
	}

	//读取全部数据
	@Test
	public void testCellType03() throws Exception {
		//获取文件流
		FileInputStream inputStream = new FileInputStream(PATH + "会员消费商品明细表.xls");
		//1,创建工作簿 代表excle能操作的他都能操作
		Workbook workbook = new HSSFWorkbook(inputStream);
		//获取标题内容
		Sheet sheetAt = workbook.getSheetAt(0);
		Row rowTitle = sheetAt.getRow(0);
		if(rowTitle!=null){
			//获取一行全部
			int cellCount = rowTitle.getPhysicalNumberOfCells();
			for (int cellNum = 0; cellNum < cellCount; cellNum++) {
				Cell cell = rowTitle.getCell(cellNum);
				if(cell != null){
					int cellType = cell.getCellType();
//					System.out.print(cellType);
					String stringCellValue = cell.getStringCellValue();
					System.out.print(stringCellValue+" | ");
				}
			}
			System.out.println();
		}
		//获取表中的内容 (除了第一行之外的)
		//获取行记录
		int rowCount = sheetAt.getPhysicalNumberOfRows();
		for (int rowNum = 1; rowNum < rowCount; rowNum++) {
			Row rowData = sheetAt.getRow(rowNum);
			if (rowData != null){
				//读取列
				int cellCount = rowTitle.getPhysicalNumberOfCells();
				for (int cellNum = 0; cellNum < cellCount; cellNum++) {
					System.out.print("["+(rowNum+1)+"-"+(cellNum+1)+"]");

					Cell cell = rowData.getCell(cellNum);
					//匹配列的数据类型
					if(cell != null){
						int cellType = cell.getCellType();
						String cellValue="";

						switch (cellType){
							case HSSFCell.CELL_TYPE_STRING: //字符串
								System.out.print("String ");
								cellValue = cell.getStringCellValue();
								break;
							case HSSFCell.CELL_TYPE_BOOLEAN: //布尔
								System.out.print("boolean ");
								cellValue = String.valueOf(cell.getBooleanCellValue());
								break;
							case HSSFCell.CELL_TYPE_BLANK: //空
								System.out.print("BLANK ");
								break;
							case HSSFCell.CELL_TYPE_NUMERIC: //数组(日期,数字)
								System.out.print("NUMERIC ");
								if(HSSFDateUtil.isCellDateFormatted(cell)){//日期
									System.out.print("日期 ");
									Date date = cell.getDateCellValue();
									cellValue = new DateTime(date).toString("yyyy-MM-dd");
								}else {
									//数字,防止数字过长
									System.out.print("转换为字符串输出 ");
									cell.setCellType(HSSFCell.CELL_TYPE_STRING);
									cellValue = cell.toString();
								}
								break;
							case HSSFCell.CELL_TYPE_ERROR: //错误
								System.out.print("数据类型错误 ");
								break;
						}
						System.out.println(cellValue);
					}

				}
			}
		}
		//关闭流
		inputStream.close();
	}

	//改成工具类
//	@Test
//	public void testCellType03(FileInputStream inputStream) throws Exception {
//		//获取文件流
//		inputStream = new FileInputStream(PATH + "会员消费商品明细表.xls");
//		//1,创建工作簿 代表excle能操作的他都能操作
//		Workbook workbook = new HSSFWorkbook(inputStream);
//		//获取标题内容
//		Sheet sheetAt = workbook.getSheetAt(0);
//		Row rowTitle = sheetAt.getRow(0);
//		if(rowTitle!=null){
//			//获取一行全部
//			int cellCount = rowTitle.getPhysicalNumberOfCells();
//			for (int cellNum = 0; cellNum < cellCount; cellNum++) {
//				Cell cell = rowTitle.getCell(cellNum);
//				if(cell != null){
//					int cellType = cell.getCellType();
////					System.out.print(cellType);
//					String stringCellValue = cell.getStringCellValue();
//					System.out.print(stringCellValue+" | ");
//				}
//			}
//			System.out.println();
//		}
//		//获取表中的内容 (除了第一行之外的)
//		//获取行记录
//		int rowCount = sheetAt.getPhysicalNumberOfRows();
//		for (int rowNum = 1; rowNum < rowCount; rowNum++) {
//			Row rowData = sheetAt.getRow(rowNum);
//			if (rowData != null){
//				//读取列
//				int cellCount = rowTitle.getPhysicalNumberOfCells();
//				for (int cellNum = 0; cellNum < cellCount; cellNum++) {
//					System.out.print("["+(rowNum+1)+"-"+(cellNum+1)+"]");
//
//					Cell cell = rowData.getCell(cellNum);
//					//匹配列的数据类型
//					if(cell != null){
//						int cellType = cell.getCellType();
//						String cellValue="";
//
//						switch (cellType){
//							case HSSFCell.CELL_TYPE_STRING: //字符串
//								System.out.print("String ");
//								cellValue = cell.getStringCellValue();
//								break;
//							case HSSFCell.CELL_TYPE_BOOLEAN: //布尔
//								System.out.print("boolean ");
//								cellValue = String.valueOf(cell.getBooleanCellValue());
//								break;
//							case HSSFCell.CELL_TYPE_BLANK: //空
//								System.out.print("BLANK ");
//								break;
//							case HSSFCell.CELL_TYPE_NUMERIC: //数组(日期,数字)
//								System.out.print("NUMERIC ");
//								if(HSSFDateUtil.isCellDateFormatted(cell)){//日期
//									System.out.print("日期 ");
//									Date date = cell.getDateCellValue();
//									cellValue = new DateTime(date).toString("yyyy-MM-dd");
//								}else {
//									//数字,防止数字过长
//									System.out.print("转换为字符串输出 ");
//									cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//									cellValue = cell.toString();
//								}
//								break;
//							case HSSFCell.CELL_TYPE_ERROR: //错误
//								System.out.print("数据类型错误 ");
//								break;
//						}
//						System.out.println(cellValue);
//					}
//
//				}
//			}
//		}
//		//关闭流
//		inputStream.close();
//	}

	@Test
	public void testFormula() throws Exception {
		////获取文件流
		FileInputStream inputStream = new FileInputStream(PATH + "计算公式.xls");
		//创建工作表
		Workbook workbook = new HSSFWorkbook(inputStream);
		Sheet sheetAt = workbook.getSheetAt(0);

		Row row = sheetAt.getRow(4);
		Cell cell = row.getCell(0);

		//拿到计算公式
		FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
		//输出单元格的内容
		int cellType = cell.getCellType();
		switch (cellType){
			case Cell.CELL_TYPE_FORMULA: //公式
				String cellFormula = cell.getCellFormula();
				System.out.println(cellFormula);

				//计算
				CellValue evaluate = formulaEvaluator.evaluate(cell);
				String cellValue = evaluate.formatAsString();
				System.out.println(cellValue);
				break;
		}
	}




}
